package com.deyuanyun.pic.insp.util;

import com.deyuanyun.pic.common.spring.SpringAppliction;
import com.deyuanyun.pic.common.util.RequestUtil;
import com.deyuanyun.pic.common.util.ajax.AjaxResponse;
import com.deyuanyun.pic.common.util.web.DownLoadUtil;
import com.deyuanyun.pic.common.util.web.RequestResponseContext;
import com.deyuanyun.pic.pipe.util.ExportExcel;
import com.deyuanyun.pic.settings.controller.dto.org.CompanyLogoVO;
import com.deyuanyun.pic.settings.service.org.CompanyService;
import com.google.common.collect.HashBasedTable;
import com.google.common.collect.Lists;
import com.google.common.collect.Table;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.jeecgframework.poi.excel.ExcelExportUtil;
import org.jeecgframework.poi.excel.entity.TemplateExportParams;
import org.jeecgframework.poi.util.PoiMergeCellUtil;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.util.*;

/**
 * 模块描述
 *
 * @author scalpel.王敏
 * @version [v1.0] 创建时间（2016/10/18）
 * @see // 引用的类
 * Method List:
 * 1.----------------
 * 2.----------------
 * History:
 */
public class InspExportUtil {

    /**
     * 这是关于内检测导出的工具类
     * 我是这样想的：
     *      因为每个内检测类型的数据字段都不一样，而每个内检测数据类型的查询都有参考信息，
     *      多数情况下，内检测信息的字段在参考信息中都木有
     *      所以我打算把内检测各个类型需要导出的字段在LinkedHashMap中定义好，根据 key = head value=filed
     *      然后把参考信息中有的字段也在自己的map中定义好，如果没有就 '-'或者0代替
     *
     */
    static Table<Integer,String, String> exportFiled = HashBasedTable.create();

    /**
     * 参考表信息
     */
    static HashMap<String ,String> referenceMap = new HashMap<String ,String>();
    static  int row = 0;

    /**
     * 数字类型字段
     */
    static LinkedList<String> numberFiled = Lists.newLinkedList();


    static LinkedList<String> shareFiled = Lists.newLinkedList();
    /**
     * 内检测检测结果查询数据
     */
    static
    {
        //内检测结果查询除开环焊缝以外，其他的缺陷都需要先匹配公共字段，状态里面是1，然后在是特征状态
        //========================================================>
        //导出字段初始化



        exportFiled.put(++row,"i.FEATURE_NUMBER","1!PATCH");//"缺陷编号"
        exportFiled.put(++row,"i.FEATURE_NUMBER","GIRTHWELD");//"环焊缝检测编号"
        exportFiled.put(++row,"i.FEATURE_NUMBER","PATCH");//"修补补丁编号"
        exportFiled.put(++row,"feature.SHORT_NAME","1,!ELLIPTIC,!PATCH");//"缺陷类型"
        exportFiled.put(++row,"'环焊缝异常'","GIRTHWELD");//"缺陷类型"
        exportFiled.put(++row,"'椭圆变形'","ELLIPTIC");//"缺陷类型"
        exportFiled.put(++row,"'补丁'","PATCH");//"缺陷类型"
        exportFiled.put(++row,"featurec.SHORT_NAME","METALLOSS");//"缺陷子类型"
        exportFiled.put(++row,"i.MILEAGE","1");//"绝对距离"
        exportFiled.put(++row,"i.MILEAGE","GIRTHWELD");//"绝对距离"
        exportFiled.put(++row,"i.REFERENCE","1");//"最近参考点"
        exportFiled.put(++row,"i.REFERENCE","GIRTHWELD");//"最近参考点"
        exportFiled.put(++row,"i.REFERENCE_DISTANCE","1");//"相对最近参考点距离"
        exportFiled.put(++row,"i.REFERENCE_DISTANCE","GIRTHWELD");//"相对最近参考点距离"
        exportFiled.put(++row,"i.UGW_NUMBER","1,GIRTHWELD");//"上游环焊缝编号"
        exportFiled.put(++row,"i.DISTANCETOUGW_NUMBER","1,GIRTHWELD");//"相对上游环焊缝距离"

        exportFiled.put(++row,"i.RAW_B","1,GIRTHWELD");//"b"
        exportFiled.put(++row,"i.RAW_L","1,GIRTHWELD");//"l"
        exportFiled.put(++row,"i.RAW_H","1,GIRTHWELD");//"h"

        exportFiled.put(++row,"i.RAW_X","1,GIRTHWELD");//"X"
        exportFiled.put(++row,"i.RAW_Y","1,GIRTHWELD");//"Y"
        exportFiled.put(++row,"i.RAW_Z","1,GIRTHWELD");//"Z"
        exportFiled.put(++row,"i.MAX_DEPTH","METALLOSS,DEPRESSED,OTHER");//"峰值深度"
        exportFiled.put(++row,"i.CRACK_DEPTH","CRACK");//"类裂纹深度"
        exportFiled.put(++row,"i.DEPTH_PERCENT","CRACK");//"类裂纹深度百分比"
        exportFiled.put(++row,"i.LENGTH","METALLOSS,DEPRESSED,GIRTHWELD,ZWELD,CRACK,,GIRTHWELD");//"长度"
        exportFiled.put(++row,"i.LENGTH_","OTHER");//"长度"
        exportFiled.put(++row,"i.WIDTH","METALLOSS,DEPRESSED,GIRTHWELD,ZWELD,CRACK,GIRTHWELD,OTHER");//"宽度"
        exportFiled.put(++row,"i.ERF","METALLOSS,");//"ERF"
        exportFiled.put(++row,"i.CLOCK","METALLOSS,DEPRESSED,ZWELD,CRACK,OTHER,,ZWELD,PATCH,GIRTHWELD");//"时钟位置"
        exportFiled.put(++row,"i.MAX_DEFORMATION","ELLIPTIC");//"最大变形量"
        exportFiled.put(++row,"i.LENGTH_","ELLIPTIC");//"最小变形量"
        exportFiled.put(++row,"i.CREATE_REMARK","METALLOSS,DEPRESSED,CRACK,OTHER,GIRTHWELD,ZWELD,PATCH,ELLIPTIC");//"备注"
        //导出字段初始化完成

        //数字类型字段
        numberFiled.add("i.MILEAGE");
        numberFiled.add("i.RAW_X");
        numberFiled.add("i.RAW_Y");
        numberFiled.add("i.RAW_Z");
        numberFiled.add("i.REFERENCE_DISTANCE");
        numberFiled.add("i.MAX_DEPTH");
        numberFiled.add("i.LENGTH");
        numberFiled.add("i.LENGTH_");
        numberFiled.add("i.WIDTH");
        numberFiled.add("i.MAX_DEFORMATION");
        numberFiled.add("i.MIN_DEFORMATION");
        numberFiled.add("i.DISTANCETOUGW_NUMBER");
        numberFiled.add("i.ERF");
        numberFiled.add("i.CRACK_DEPTH");
        numberFiled.add("i.DEPTH_PERCENT");


        //公共模块
        shareFiled.add("i.MILEAGE");//绝对距离
        shareFiled.add("i.REFERENCE");//最近参考点
        shareFiled.add("i.REFERENCE_DISTANCE");//相对最近参考点距离
        shareFiled.add("i.UGW_NUMBER");//上游环焊缝编号
        shareFiled.add("i.DISTANCETOUGW_NUMBER");//相对上游环焊缝距离
        shareFiled.add("i.RAW_B");
        shareFiled.add("i.RAW_L");
        shareFiled.add("i.RAW_H");
        shareFiled.add("i.RAW_X");
        shareFiled.add("i.RAW_Y");
        shareFiled.add("i.RAW_Z");
    }

    /**
     * 内检测参考信息
     */
    static
    {
        referenceMap.put("item1","PIC_INSP_PIPE_SECTION".toUpperCase());
        referenceMap.put("item2","PIC_INSP_ELBOW".toUpperCase());
        referenceMap.put("item3","PIC_INSP_TEE".toUpperCase());
        referenceMap.put("item4","PIC_INSP_VALVE".toUpperCase());

    }

    /**
     * METALLOSS 金属损失
     * DEPRESSED 凹陷
     * GIRTHWELD 环焊缝
     * WELD 焊缝
     * CRACK 类裂纹
     * OTHER 其他缺陷
     * PATCH 修补补丁
     * ELLIPTIC 椭圆变形
     * @param characteristic
     * @return
     */
    public static String getFiledList(String characteristic)
    {
        StringBuffer fileds = new StringBuffer();
        fileds.append(1).append(" as t ,");
        int cell = 0;
        for(int i=1,e=exportFiled.size();i <= e;i++)
        {
            Iterator<Map.Entry<String, String>> it =exportFiled.row(i).entrySet().iterator();
            while (it.hasNext())
            {
                Map.Entry<String, String> entry = it.next();
                //当不是环焊缝的时候，我们判断value里面包含1就是公共的字段
                if(!"GIRTHWELD".equals(characteristic))
                {
                    if(StringUtils.contains(entry.getValue(),"1") && !StringUtils.contains(entry.getValue(),"!"+characteristic))//
                    {
                        fileds.append(entry.getKey()).append(" as c").append(++cell).append(",");
                    }
                }

                //对环焊缝和非环焊缝都适合
                if(  StringUtils.contains(entry.getValue(),characteristic) && !StringUtils.contains(entry.getValue(),"!"+characteristic) )
                {
                    fileds.append(entry.getKey()).append(" as c").append(++cell).append(",");
                }
            }
        }

        //删除最后的 , 号
        if(fileds.length() > 0)
        {
            fileds.deleteCharAt(fileds.length()-1);
        }
        return fileds.toString();
    }



    /**
     * 获取参考信息sql
     * @param filedList
     * @param param
     * @return
     */
    public static String getReference(String filedList,Map<String,Object> param)
    {
        param.put("orderBy","c3");
        List<String> items= (List<String>) param.get("otherList");
        StringBuffer result =new StringBuffer();

        if (items != null )
        {
            String type="0";
            for (String item:items)
            {
                if("20".equals(item))
                {
                    continue;
                }
                result.append(" union all ");
                param.put(referenceMap.get("item"+item),getReferenceSql(filedList,"item"+item,param));
//                result.append(getReferenceSql(filedList,"item"+item,param));
            }
        }
        return result.toString();
    }




    /**
     * 获取参考
     * @param filedList
     * @param type
     * @return
     */
    final static String getReferenceSql(String filedList,String type,Map<String,Object> param)
    {


        StringBuffer result = new StringBuffer("select  ");
        result.append(0).append(" as t ,");
        if("item3".equals(type))
        {
            //三通
            result.append("i.INSP_NUMBER as c1").append(",").append("'三通' as c2 ,");
        }else if("item4".equals(type))
        {
            //阀门
            result.append("i.INSP_NUMBER as c1").append(",").append("'阀门' as c2 ,");
        }else if("item1".equals(type))
        {
            //环焊缝
            result.append("i.GW_NUMBER as c1").append(",").append("'环焊缝' as c2 ,");
        }else if("item2".equals(type))
        {
            //弯头
            result.append("i.INSP_NUMBER as c1").append(",").append("case WHEN i.ELBOW_TYPE is null THEN '弯头' ELSE  c.short_name END as c2 ,");
        }

        //绝对距离
//        result.append("i.MILEAGE as c3 , ");


        String list[] = filedList.split(",");
        for(int i=3 ,e=list.length ; i < e; i++)
        {
            String filedName[] = list[i].split("as");

            if(shareFiled.contains(filedName[0].trim()))
            {
                result.append(filedName[0]+" as c").append((i)).append(" ,");
            }else if(numberFiled.contains(filedName[0].trim()))
            {
                result.append("0 as c").append((i)).append(" ,");

            }else{
                result.append("'' as c").append((i)).append(" ,");

            }


        }

        //删除结尾处的 , 号
        if(result.length() >0)
        {
            result.deleteCharAt(result.length()-1);
        }
        result.append(" from ").append(referenceMap.get(type)).append(" i");
        if("item2".equals(type)) {

            result.append(" left join SYS_BASE_CODE c on (c.ID_ = i.ELBOW_TYPE) ");
        }

        result.append(" where ").append(getWhere(param));
        return result.toString();
    }


    /**
     * 获取查询条件
     * @return
     */
    final  static String getWhere(Map<String,Object> param)
    {

        StringBuffer result = new StringBuffer(" i.DELETED=0 ");

        //检测任务条件
        if(!com.deyuanyun.pic.common.util.StringUtils.isEmpty((String)param.get("taskId")))
        {
            result.append(" and i.INSP_TASK_ID = '").append(param.get("taskId")).append("' ");
        }
        //管道条件
        if(!com.deyuanyun.pic.common.util.StringUtils.isEmpty((String)param.get("lineId")))
        {
            result.append(" and i.PIPE_LINE_ID = '").append(param.get("lineId")).append("' ");
        }

        //构建绝对距离查询条件
        if(param.get("beginMileage") != null && param.get("beginMileage") == param.get("endMileage"))
        {
            result.append(" and i.MILEAGE =  ").append(param.get("endMileage")).append(" ");
        }

        if(param.get("beginMileage") != null)
        {
            result.append(" and i.MILEAGE >= ").append(param.get("beginMileage")).append(" ");
        }

        if(param.get("endMileage") !=null  &&  param.get("beginMileage") != param.get("endMileage"))
        {
            result.append(" and i.MILEAGE < ").append(param.get("endMileage")).append(" ");
        }
        return result.toString();
    }

    public static void main(String args[])
    {
        String filed="i.CUSTOM_NUMBER as c1,feature.SHORT_NAME as c3,i.MILEAGE as c5,i.REFERENCE as c7,i.REFERENCE_DISTANCE as c8,i.UGW_NUMBER as c9,i.DISTANCETOUGW_NUMBER as c10,i.RAW_X as c11,i.RAW_Y as c12,i.RAW_Z as c13,i.MAX_DEPTH as c14,i.LENGTH as c15,i.WIDTH as c16,i.ERF as c17,i.CLOCK as c18";
        System.out.println(filed.split(",").length);
        String fileds[]=filed.split(",");
        for(int i=0,e=fileds.length;i<e;i++)
        {
            System.out.println(fileds[i]);
        }
    }



    public final  static  void exportExcel(String templateName,Map param,HttpServletResponse response,String excelName) throws IOException {
        String path = "";
        if(templateName.indexOf("classes") == -1) {
            path = InspExportUtil.class.getResource("/").getPath();
            if(path.indexOf(":") == 2)
            {
                path = path.substring(1);
                path = path.substring(0,path.length()-1);
             }
        }
        ExportExcel.exportExcel(path + templateName,param,response,excelName);
    }

    public final  static  Workbook exportExcelToZip(String templateName,Map param,HttpServletResponse response,String excelName) throws IOException {
        String path = InspExportUtil.class.getResource("/").getPath();
        if(path.indexOf(":") == 2)
        {
            path = path.substring(1);
        }

        return  ExportExcel.exportExcelReturnWrokBook(path + templateName,param,response,excelName);
    }


    public final  static void  exportApprarse(String excelName,
                                              List<List<String[]>> list, HttpServletResponse response,
                                              HttpServletRequest request)
    {

        HashMap param = RequestUtil.parseRequestParamToHashMap(request);

        List data = new ArrayList();

        List<String[]> apprarses = list.get(0);
        for(String[] rows:apprarses)
        {
            Map row = new HashMap();
            for(int i=0,e=rows.length ;i<e;i++)
            {
                row.put("C"+(i+1),rows[i]);
            }
            data.add(row);
        }
        param.put("data",data);
        String tempPath = InspExportUtil.class.getResource("/").getPath()+"template/excl/insp/apprarse.xlsx";
        try {
            if(tempPath.indexOf(":") == 2)
            {
                tempPath = tempPath.substring(1);
            }

            ExportExcel.exportExcel(tempPath,param,response,excelName);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
